<?php
require_once 'Plots.php';

function vsetky_skoly_v_kraji()
{
  $select = "SELECT k.nazov,COUNT(DISTINCT s.school_id) AS vsetky_skoly_v_kraji FROM schools AS s
  LEFT JOIN kraj AS k ON k.id = s.kraj_id
  GROUP BY s.kraj_id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['nazov']] = array($row['vsetky_skoly_v_kraji']);
  }
  return $data;
}

function vsetky_skoly_podla_typu()
{
  $select = "SELECT c.category_name AS cat,COUNT(DISTINCT s.school_id) AS vsetky_skoly_typu FROM schools AS s
  LEFT JOIN categories AS c ON c.category_id = s.category_id
  GROUP BY c.category_id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['cat']] = array($row['vsetky_skoly_typu']);
  }
  return $data;
}

function casOdpovede()
{
  $baseTime = 1305154257;
  $select = 'SELECT * FROM logs';
  $data = array();
  for($i=0;$i<220;$i++)
  {
    $data[$i]=array(0,0);
  }
  foreach(Db::fetchAll($select) as $row)
  {
    $hour = ($row['timestamp'] - $baseTime) / 3600;
    if($hour>219)continue;
    if($row['action']=="form render")
    {
      $data[$hour][1] ++;
    }
    if($row['action']=="form post")
    {
      $data[$hour][0] ++;
      $data[$hour][1] --;
    }
  }
  $plot = multiLineChart($data);
  $plot->setTitle("Odoslania vs neodoslania za hodinu");
  $plot->SetXTickIncrement(24);
  $plot->SetOutputFile('casOdpovede.png');
  $plot->DrawGraph();
}

function krajSkoly()
{
  $data = vsetky_skoly_v_kraji();
  $plot = multiBarChart($data);
  $plot->SetYTickIncrement(100);
  $plot->SetPlotAreaWorld(NULL, 0, NULL, NULL);
  $plot->setTitle("Pocty skol v jednotlivych krajoch");
  $plot->SetOutputFile('krajSkoly.png');
  $plot->DrawGraph();
}

function krajOdpovede()
{  
  $select1 = "SELECT k.nazov AS nazov_kraja,COUNT(DISTINCT s.school_id) AS zodpovedane_v_kraji FROM schools_answers AS sa
  LEFT JOIN schools AS s ON s.school_id = sa.school_id
  LEFT JOIN kraj AS k ON k.id = s.kraj_id
  GROUP BY s.kraj_id";

  $select2 = "SELECT k.nazov,COUNT(DISTINCT s.school_id) AS otvorenia_v_kraji FROM logs AS l
  LEFT JOIN schools AS s ON s.school_id = l.school_id
  LEFT JOIN kraj AS k ON k.id = s.kraj_id
  WHERE l.action = 'form render'
  GROUP BY s.kraj_id";

  $vsetky_skoly_v_kraji = vsetky_skoly_v_kraji();
  $data1 = Db::fetchAll($select1);
  $data2 = Db::fetchAll($select2);
  $data = array();
  while(($row1 = each($data1)) && ($row2 = each($data2)))
  {
    $percent_zodpovedane_v_kraji = ($row1[1]['zodpovedane_v_kraji']*100) / ($vsetky_skoly_v_kraji[$row1[1]['nazov_kraja']][0]);
    $percent_otvorene_v_kraji = $row2[1]['otvorenia_v_kraji']*100 / $vsetky_skoly_v_kraji[$row1[1]['nazov_kraja']][0];
    $percent_nezodpovednych_v_kraji = $percent_otvorene_v_kraji - $percent_zodpovedane_v_kraji;
    $data[$row1[1]['nazov_kraja']] = array($percent_zodpovedane_v_kraji, $percent_nezodpovednych_v_kraji);
  }
  $plot = multiBarChart($data);
  $plot->SetYLabelType('data',1,'','%');
  $plot->setTitle("Zodpovedane vs Nezodpovedane v jednotlivych krajoch");
  $plot->SetOutputFile('krajOdpovede.png');
  $plot->DrawGraph();
}

function krajOchota()
{
  $select1 = "SELECT k.nazov AS nazov_kraja,COUNT(DISTINCT s.school_id) AS zodpovedane_v_kraji FROM schools_answers AS sa
  LEFT JOIN schools AS s ON s.school_id = sa.school_id
  LEFT JOIN kraj AS k ON k.id = s.kraj_id
  GROUP BY s.kraj_id";
  
  $select2 = "SELECT k.nazov,COUNT(DISTINCT s.school_id) AS otvorenia_v_kraji FROM logs AS l
  LEFT JOIN schools AS s ON s.school_id = l.school_id

  LEFT JOIN kraj AS k ON k.id = s.kraj_id
  WHERE l.action = 'form render'
  GROUP BY s.kraj_id";
  
  $data1 = Db::fetchAll($select1);
  $data2 = Db::fetchAll($select2);
  
  $data = array();
  while(($row1 = each($data1)) && ($row2 = each($data2)))
  {
    $data[$row1[1]['nazov_kraja']] = array($row1[1]['zodpovedane_v_kraji']*100/$row2[1]['otvorenia_v_kraji']);
  }
  $plot = multiBarChart($data);
  $plot->SetYLabelType('data',1,'','%');
  $plot->setTitle("Ochota v jednotlivych krajoch");
  $plot->SetOutputFile('krajOchota.png');
  $plot->DrawGraph();
}

function typSkoly()
{
  $data = vsetky_skoly_podla_typu();
  $plot = multiBarChart($data);
  $plot->SetYTickIncrement(100);
  $plot->SetXLabelAngle(90);
  $plot->SetPlotAreaWorld(NULL, 0, NULL, NULL);
  $plot->setTitle("Pocty skol podla typu");
  $plot->SetOutputFile('typSkoly.png');
  $plot->DrawGraph();
}

function typOchota()
{
  $select1 = "SELECT c.category_name AS cat,COUNT(DISTINCT s.school_id) AS zodpovedane_v_kategorii FROM schools_answers AS sa
  LEFT JOIN schools AS s ON s.school_id = sa.school_id
  LEFT JOIN categories AS c ON c.category_id = s.category_id
  GROUP BY c.category_id";
  
  $select2 = "SELECT c.category_name AS cat,COUNT(DISTINCT s.school_id) AS otvorenia_v_kategorii FROM logs AS l
  LEFT JOIN schools AS s ON s.school_id = l.school_id
  LEFT JOIN categories AS c ON c.category_id = s.category_id
  WHERE l.action = 'form render'
  GROUP BY c.category_id";
  
  $data1 = Db::fetchAll($select1);
  $data2 = Db::fetchAll($select2);
  
  $data = array();
  while(($row1 = each($data1)) && ($row2 = each($data2)))
  {
    $data[$row1[1]['cat']] = array($row1[1]['zodpovedane_v_kategorii']*100/$row2[1]['otvorenia_v_kategorii']);
  }
  $plot = multiBarChart($data);
  $plot->SetYLabelType('data',1,'','%');
  $plot->SetXLabelAngle(90);
  $plot->setTitle("Ochota podla typu skoly");
  $plot->SetOutputFile('typOchota.png');
  $plot->DrawGraph();
}

function questionDistribution($question_id)
{
  $select = "SELECT question_string FROM questions WHERE question_id=" . $question_id;
  $row = Db::fetchRow($select);
  $question_string = $row['question_string'];
  $select1 = "SELECT a.answer_string AS ans,COUNT(DISTINCT sa.schools_answers_id) AS cnt FROM schools_answers AS sa
  LEFT JOIN answers AS a ON a.answer_id = sa.answer_id
  LEFT JOIN questions AS q ON q.question_id = a.question_id
  WHERE q.question_id=".$question_id."
  GROUP BY a.answer_id
  ORDER BY cnt DESC";
  $data = array();
  foreach(Db::fetchAll($select1) as $row)
  {
    $data[$row['ans']] = array($row['cnt']);
  }
  $plot = multiBarChart($data);
  $plot->setTitle(htmlspecialchars($question_string));
  $plot->SetOutputFile('q-'.$question_id.'.png');
  $plot->DrawGraph();
}

function krajOtvorenost()
{
  $select = "SELECT kraj.nazov,100*SUM(otv)/SUM(vs) AS percento FROM
(
SELECT vsetky.school_id,vsetky.category_id,vsetky.kraj_id,otv,vs FROM (
SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS otv
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (2,4,6,7,45,46,50)
GROUP BY s.school_id
) AS otvoreny
LEFT JOIN (
SELECT s.school_id, s.category_id, s.kraj_id, COUNT(DISTINCT sa.schools_answers_id) AS vs
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
GROUP BY s.school_id
) AS vsetky
ON vsetky.school_id = otvoreny.school_id
) AS data
LEFT JOIN kraj ON data.kraj_id=kraj.id
GROUP BY kraj.id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['nazov']] = array($row['percento']);
  }
  $plot = multiBarChart($data);
  $plot->SetPlotAreaWorld(NULL, 10, NULL, NULL);
  $plot->setTitle('Otvorenost jendotlivych krajov');
  $plot->SetOutputFile('krajOtvorenost.png');
  $plot->DrawGraph();
}

function typOtvorenost()
{
  $select = "SELECT categories.category_name,100*SUM(otv)/SUM(vs) AS percento FROM
(
SELECT vsetky.school_id,vsetky.category_id,vsetky.kraj_id,otv,vs FROM (
SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS otv
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (2,4,6,7,45,46,50)
GROUP BY s.school_id
) AS otvoreny
LEFT JOIN (
SELECT s.school_id, s.category_id, s.kraj_id, COUNT(DISTINCT sa.schools_answers_id) AS vs
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
GROUP BY s.school_id
) AS vsetky
ON vsetky.school_id = otvoreny.school_id
) AS data
LEFT JOIN categories ON data.category_id=categories.category_id
GROUP BY categories.category_id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['category_name']] = array($row['percento']);
  }
  $plot = multiBarChart($data);
  $plot->SetPlotAreaWorld(NULL, NULL, NULL, NULL);
  $plot->SetXLabelAngle(90);
  $plot->setTitle('Otvorenost podla typu skoly');
  $plot->SetOutputFile('typOtvorenost.png');
  $plot->DrawGraph();
}

function typUcely()
{
  $select = "SELECT c.category_name,SUM(vyuc)*100/COUNT(DISTINCT `data`.school_id) AS vv,SUM(bez)*100/COUNT(DISTINCT `data`.school_id) AS bb,SUM(adm)*100/COUNT(DISTINCT `data`.school_id) AS aa FROM
(
SELECT vs.school_id,vyuc,bez,adm FROM
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS vsetky
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
GROUP BY s.school_id
) AS vs
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS vyuc
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (34)
GROUP BY s.school_id
) AS v
ON vs.school_id=v.school_id
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS bez
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (35)
GROUP BY s.school_id
) AS b
ON b.school_id=vs.school_id
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS adm
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (36)
GROUP BY s.school_id
) AS a
ON a.school_id=vs.school_id
) AS `data`
LEFT JOIN schools AS s ON  `data`.school_id = s.school_id
LEFT JOIN categories AS c ON c.category_id = s.category_id
GROUP BY c.category_id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['category_name']] = array($row['vv'],$row['bb'],$row['aa']);
  }
  $plot = multiBarChart($data);
  $plot->SetPlotAreaWorld(NULL, NULL, NULL, NULL);
  $plot->SetXLabelAngle(90);
  $plot->setTitle('Ucely podla typu');
  $plot->SetOutputFile('typUcely.png');
  $plot->DrawGraph();
}

function krajUcely()
{
  $select = "SELECT k.nazov,SUM(vyuc)*100/COUNT(DISTINCT `data`.school_id) AS vv,SUM(bez)*100/COUNT(DISTINCT `data`.school_id) AS bb,SUM(adm)*100/COUNT(DISTINCT `data`.school_id) AS aa FROM
(
SELECT vs.school_id,vyuc,bez,adm FROM
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS vsetky
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
GROUP BY s.school_id
) AS vs
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS vyuc
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (34)
GROUP BY s.school_id
) AS v
ON vs.school_id=v.school_id
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS bez
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (35)
GROUP BY s.school_id
) AS b
ON b.school_id=vs.school_id
LEFT JOIN
(SELECT s.school_id, COUNT(DISTINCT sa.schools_answers_id) AS adm
FROM schools_answers AS sa
LEFT JOIN schools AS s ON s.school_id=sa.school_id
WHERE sa.answer_id IN (36)
GROUP BY s.school_id
) AS a
ON a.school_id=vs.school_id
) AS `data`
LEFT JOIN schools AS s ON  `data`.school_id = s.school_id
LEFT JOIN kraj AS k ON k.id = s.kraj_id
GROUP BY k.id";
  $data = array();
  foreach(Db::fetchAll($select) as $row)
  {
    $data[$row['nazov']] = array($row['vv'],$row['bb'],$row['aa']);
  }
  $plot = multiBarChart($data);
  $plot->SetPlotAreaWorld(NULL, NULL, NULL, NULL);
  $plot->setTitle('Ucely podla kraja');
  $plot->SetOutputFile('krajUcely.png');
  $plot->DrawGraph();
}
?>
